﻿using CodeGenerator.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Dapper;
using MySql.Data.MySqlClient;

namespace CodeGenerator.Core
{
    public class MySqlDatabase : IDatabase
    {
        /// <summary>
        /// 测试数据库链接
        /// </summary>
        /// <param name="server"></param>
        /// <param name="errMessage"></param>
        /// <returns></returns>
        public bool TestConnnect(ServerInfo server, out string errMessage)
        {
            string connectionString = Config.GetConnectionString(server, "mySql");

            try
            {
                using (IDbConnection conn = new MySqlConnection(connectionString))
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    errMessage = string.Empty;
                    return true;
                }
            }
            catch (SqlException err)
            {
                errMessage = err.Message;
                return false;
            }
        }

        public IList<string> GetDatabaseList(ServerInfo server)
        {
            IList<string> dbList = new List<string>();
            if (!string.IsNullOrEmpty(server.DatabaseName))
            {
                dbList.Add(server.DatabaseName);
            }
            else
            {
                string strSql = "Select SCHEMA_NAME as 'name' From INFORMATION_SCHEMA.SCHEMATA";

                using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server, "mySql")))
                {
                    while (dr.Read())
                    {
                        dbList.Add(dr["name"].ToString());
                    }
                }
            }
            return dbList;
        }

        public IList<TableInfo> GetTableList(ServerInfo server, string dbName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(@"SELECT TABLE_NAME,TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES  WHERE table_schema='{0}'", dbName);

            using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server, dbName)))
            {
                IList<TableInfo> tbList = new List<TableInfo>();

                while (dr.Read())
                {
                    TableInfo table = new TableInfo();
                    table.Name = dr["TABLE_NAME"].ToString();
                    table.Comments = dr["TABLE_COMMENT"].ToString(); ;

                    tbList.Add(table);
                }
                return tbList;
            }
        }

        public List<ColumnInfo> GetColumnInfoList(ServerInfo server, string dbName, string tableName)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat(@"SELECT 
                                            COLUMN_NAME,
                                            COLUMN_COMMENT,
                                            ORDINAL_POSITION,
                                            IS_NULLABLE,
                                            CHARACTER_MAXIMUM_LENGTH,
                                            COLUMN_KEY,
                                            COLUMN_DEFAULT,
                                            COLUMN_COMMENT,
                                            COLUMN_TYPE,
                                            DATA_TYPE,
                                            NUMERIC_PRECISION,
                                            EXTRA
                                    FROM INFORMATION_SCHEMA.COLUMNS 
                                    WHERE TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'
                                    ORDER BY ORDINAL_POSITION", dbName, tableName);


                using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server, dbName)))
                {
                    List<ColumnInfo> list = new List<ColumnInfo>();

                    while (dr.Read())
                    {
                        ColumnInfo columnInfo = new ColumnInfo();
                        columnInfo.Id = dr["ORDINAL_POSITION"].ToString();
                        columnInfo.Name = dr["COLUMN_NAME"].ToString();
                        columnInfo.DataType = dr["DATA_TYPE"].ToString();
                        columnInfo.AutoIncrement = dr["EXTRA"].ToString() == "auto_increment" ? "是" : "否";
                        int num = 0;
                        if (int.TryParse(dr["CHARACTER_MAXIMUM_LENGTH"].ToString(), out num))
                        {
                            columnInfo.MaxLength = num;
                        }
                        ;
                        if (int.TryParse(dr["NUMERIC_PRECISION"].ToString(), out num))
                        {
                            columnInfo.DecimalNum = num;
                        }
                        columnInfo.DefaultValue = dr["COLUMN_DEFAULT"].ToString();
                        columnInfo.AllowDBNull = dr["IS_NULLABLE"].ToString() == "YES" ? "是" : "否";
                        columnInfo.IsPrimaryKey = dr["COLUMN_KEY"].ToString() == "PRI" ? "是" : "否";
                        columnInfo.Comments = dr["COLUMN_COMMENT"].ToString();

                        columnInfo.NetType = this.GetNetType(columnInfo.DataType, columnInfo.AllowDBNull);
                        columnInfo.JavaType = this.GetJavaType(columnInfo.DataType);

                        columnInfo.IsNumeric = DataTypeHelper.IsNumeric(columnInfo.NetType.Alias);
                        columnInfo.IsDateTime = DataTypeHelper.IsDateTime(columnInfo.NetType.Alias);
                        columnInfo.IsBoolean = DataTypeHelper.IsBoolean(columnInfo.NetType.Alias);

                        columnInfo.UIControlType = DataTypeHelper.GetUIControlType(columnInfo);

                        list.Add(columnInfo);
                    }
                    return list;
                }

            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取表结构信息：", ex);
                return null;
            }
        }

        public List<ColumnInfo> GetNHibernateColumnInfoList(ServerInfo server, string dbName, string tableName)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat(@"SELECT 
                                            COLUMN_NAME,
                                            COLUMN_COMMENT,
                                            ORDINAL_POSITION,
                                            IS_NULLABLE,
                                            CHARACTER_MAXIMUM_LENGTH,
                                            COLUMN_KEY,
                                            COLUMN_DEFAULT,
                                            COLUMN_COMMENT,
                                            COLUMN_TYPE,
                                            DATA_TYPE,
                                            NUMERIC_PRECISION,
                                            EXTRA
                                    FROM INFORMATION_SCHEMA.COLUMNS 
                                    WHERE TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'
                                    ORDER BY ORDINAL_POSITION", dbName, tableName);


                using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server, dbName)))
                {
                    List<ColumnInfo> list = new List<ColumnInfo>();

                    while (dr.Read())
                    {
                        ColumnInfo columnInfo = new ColumnInfo();
                        columnInfo.Id = dr["ORDINAL_POSITION"].ToString();
                        columnInfo.Name = dr["COLUMN_NAME"].ToString();
                        columnInfo.DataType = dr["DATA_TYPE"].ToString();
                        columnInfo.AutoIncrement = dr["EXTRA"].ToString() == "auto_increment" ? "是" : "否";
                        int num = 0;
                        if (int.TryParse(dr["CHARACTER_MAXIMUM_LENGTH"].ToString(), out num))
                        {
                            columnInfo.MaxLength = num;
                        }

                        if (dr["DATA_TYPE"].ToString()=="tinyint")
                        {
                            if (dr["COLUMN_TYPE"].ToString()== "tinyint(1)")
                            {
                                columnInfo.MaxLength = 1;
                            }
                        }

                        if (int.TryParse(dr["NUMERIC_PRECISION"].ToString(), out num))
                        {
                            columnInfo.DecimalNum = num;
                        }
                        columnInfo.DefaultValue = dr["COLUMN_DEFAULT"].ToString();
                        columnInfo.AllowDBNull = dr["IS_NULLABLE"].ToString() == "YES" ? "是" : "否";
                        columnInfo.IsPrimaryKey = dr["COLUMN_KEY"].ToString() == "PRI" ? "是" : "否";
                        columnInfo.Comments = dr["COLUMN_COMMENT"].ToString();

                        columnInfo.NetType = this.GetNHibernateType(columnInfo.DataType, columnInfo.AllowDBNull,columnInfo.MaxLength);
                        columnInfo.JavaType = this.GetJavaType(columnInfo.DataType);

                        columnInfo.IsNumeric = DataTypeHelper.IsNumeric(columnInfo.NetType.Alias);
                        columnInfo.IsDateTime = DataTypeHelper.IsDateTime(columnInfo.NetType.Alias);
                        columnInfo.IsBoolean = DataTypeHelper.IsBoolean(columnInfo.NetType.Alias);

                        columnInfo.UIControlType = DataTypeHelper.GetUIControlType(columnInfo);

                        list.Add(columnInfo);
                    }
                    return list;
                }

            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取表结构信息：", ex);
                return null;
            }
        }

        /// <summary>
        /// 获取表说明
        /// </summary>
        /// <param name="tableName">表明</param>
        /// <returns></returns>
        public string GetTableComments(ServerInfo server, string dbName, string tableName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(" SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='{0}' AND TABLE_NAME='{1}'", dbName, tableName);

            string result = "";
            using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server)))
            {
                while (dr.Read())
                {
                    if (dr["TABLE_COMMENT"] != null)
                        result = dr["TABLE_COMMENT"].ToString();
                }
                return result;
            }
        }

        #region 类型转换


        /// <summary>
        /// SQL to ShengYi Nhibernate 框架类型
        /// </summary>
        /// <param name="typeName"></param>
        /// <param name="isNull"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public NameElement GetNHibernateType(string typeName, string isNull, int length)
        {
            NameElement name = new NameElement();
            switch (typeName.Trim().ToLower())
            {
                case "bit":
                    name.Alias = "bool";
                    name.Name = "System.Boolean";
                    name.JdbcType = SqlDbType.Bit.ToString();
                    break;
                case "bool":
                    name.Alias = "bool";
                    name.Name = "System.Boolean";
                    name.JdbcType = SqlDbType.Bit.ToString();
                    break;
                case "tinyint":
                    if (length == 1)
                    {
                        name.Alias = "bool";
                        name.Name = "System.Boolean";
                        name.JdbcType = SqlDbType.TinyInt.ToString();
                    }
                    else
                    {
                        name.Alias = "int";
                        name.Name = "System.Int32";
                        name.JdbcType = SqlDbType.Int.ToString();
                    }
                    break;
                case "smallint":
                    name.Alias = "short";
                    name.Name = "System.Int16";
                    name.JdbcType = SqlDbType.SmallInt.ToString();
                    break;
                case "bigint":
                    name.Alias = "long";
                    name.Name = "System.Int64";
                    name.JdbcType = SqlDbType.BigInt.ToString();
                    break;
                case "int":
                    name.Alias = "int";
                    name.Name = "System.Int32";
                    name.JdbcType = SqlDbType.Int.ToString();
                    break;
                case "float":
                    name.Alias = "float";
                    name.Name = "System.Float";
                    name.JdbcType = SqlDbType.Float.ToString();
                    break;
                case "double":
                    name.Alias = "double";
                    name.Name = "System.Double";
                    name.JdbcType = SqlDbType.Float.ToString();
                    break;
                case "decimal":
                    name.Alias = "decimal";
                    name.Name = "System.Decimal";
                    name.JdbcType = SqlDbType.Decimal.ToString();
                    break;
                case "char":
                    if (length == 36)
                    {
                        name.Alias = "Guid";
                        name.Name = "System.Guid";
                        name.JdbcType = SqlDbType.Char.ToString();
                    }
                    else
                    {
                        name.Alias = "string";
                        name.Name = "System.String";
                        name.JdbcType = SqlDbType.Char.ToString();
                    }
                    break;
                case "varchar":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Text.ToString();
                    break;
                case "text":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Text.ToString();
                    break;
                case "longtext":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Text.ToString();
                    break;
                case "date":
                    name.Alias = "DateTime";
                    name.Name = "System.DateTime";
                    name.JdbcType = SqlDbType.Date.ToString();
                    break;
                case "datetime":
                    name.Alias = "DateTime";
                    name.Name = "System.DateTime";
                    name.JdbcType = SqlDbType.DateTime.ToString();
                    break;
                case "time":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Time.ToString();
                    break;
            }
            return name;
        }       

        /// <summary>
        /// Sql类型转C#类型
        /// </summary>
        /// <param name="typeName"></param>
        /// <param name="isNull"></param>
        /// <returns></returns>
        public NameElement GetNetType(string typeName, string isNull)
        {
            NameElement name = new NameElement();
            switch (typeName.Trim().ToLower())
            {
                case "bit":
                    //name.Alias = isNull == "是" ? "bool?" : "bool";
                    //name.Name = isNull == "是" ? "System.Boolean?" : "System.Boolean";
                    name.Alias = "bool";
                    name.Name = "System.Boolean";
                    name.JdbcType = SqlDbType.Bit.ToString();
                    break;
                case "bool":
                    //name.Alias = isNull == "是" ? "bool?" : "bool";
                    //name.Name = isNull == "是" ? "System.Boolean?" : "System.Boolean";                    
                    name.Alias = "bool";
                    name.Name = "System.Boolean";
                    name.JdbcType = SqlDbType.Bit.ToString();
                    break;
                case "tinyint":
                    //name.Alias = isNull == "是" ? "byte?" : "byte";
                    //name.Name = isNull == "是" ? "System.Byte" : "System.Byte";
                    name.Alias = "byte";
                    name.Name = "System.Byte";
                    name.JdbcType = SqlDbType.TinyInt.ToString();
                    break;
                case "smallint":
                    //name.Alias = isNull == "是" ? "short?" : "short";
                    //name.Name = isNull == "是" ? "System.Int16?" : "System.Int16";
                    name.Alias = "short";
                    name.Name = "System.Int16";
                    name.JdbcType = SqlDbType.SmallInt.ToString();
                    break;
                case "bigint":
                    //name.Alias = isNull == "是" ? "long?" : "long";
                    //name.Name = isNull == "是" ? "System.Int64?" : "System.Int64";
                    name.Alias = "long";
                    name.Name = "System.Int64";
                    name.JdbcType = SqlDbType.BigInt.ToString();
                    break;
                case "int":
                    //name.Alias = isNull == "是" ? "int?" : "int";
                    //name.Name = isNull == "是" ? "System.Int32?" : "System.Int32";
                    name.Alias = "int";
                    name.Name = "System.Int32";
                    name.JdbcType = SqlDbType.Int.ToString();
                    break;
                case "float":
                    //name.Alias = isNull == "是" ? "float?" : "float";
                    //name.Name = isNull == "是" ? "System.Float?" : "System.Float";
                    name.Alias = "float";
                    name.Name = "System.Float";
                    name.JdbcType = SqlDbType.Float.ToString();
                    break;
                case "double":
                    //name.Alias = isNull == "是" ? "double?" : "double";
                    //name.Name = isNull == "是" ? "System.Double?" : "System.Double";
                    name.Alias = "double";
                    name.Name = "System.Double";
                    name.JdbcType = SqlDbType.Float.ToString();
                    break;
                case "decimal":
                    //name.Alias = isNull == "是" ? "decimal?" : "decimal";
                    //name.Name = isNull == "是" ? "System.Decimal?" : "System.Decimal";
                    name.Alias = "decimal";
                    name.Name = "System.Decimal";
                    name.JdbcType = SqlDbType.Decimal.ToString();
                    break;
                case "char":
                    //name.Alias = "string";
                    //name.Name = "System.String";
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Char.ToString();
                    break;
                case "varchar":
                    //name.Alias = "string";
                    //name.Name = "System.String";
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.VarChar.ToString();
                    break;
                case "text":
                    //name.Alias = "string";
                    //name.Name = "System.String";
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Text.ToString();
                    break;
                case "longtext":
                    //name.Alias = "string";
                    //name.Name = "System.String";
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Text.ToString();
                    break;
                case "date":
                    //name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    //name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.Alias = "DateTime";
                    name.Name = "System.DateTime";
                    name.JdbcType = SqlDbType.Date.ToString();
                    break;
                case "datetime":
                    //name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    //name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.Alias = "DateTime";
                    name.Name = "System.DateTime";
                    name.JdbcType = SqlDbType.DateTime.ToString();
                    break;
                case "time":
                    //name.Alias = "string";
                    //name.Name = "System.String";
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Time.ToString();
                    break;
            }
            return name;
        }

        /// <summary>
        /// Sql类型转Java类型
        /// </summary>
        /// <param name="typeName"></param>
        /// <param name="isNull"></param>
        /// <returns></returns>
        public NameElement GetJavaType(string typeName)
        {
            NameElement name = new NameElement();
            switch (typeName.Trim().ToLower())
            {
                case "bit":
                    name.Alias = "Boolean";
                    name.Name = "java.lang.Boolean";
                    name.JdbcType = "BOOLEAN";
                    break;
                case "bool":
                    name.Alias = "Boolean";
                    name.Name = "java.lang.Boolean";
                    name.JdbcType = "BOOLEAN";
                    break;
                case "tinyint":
                    name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "INTEGER";
                    break;
                case "smallint":
                    name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "INTEGER";
                    break;
                case "bigint":
                    name.Alias = "BigInteger";
                    name.Name = "ava.math.BigInteger";
                    name.JdbcType = "BIGINT";
                    break;
                case "int":
                    name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "INTEGER";
                    break;
                case "float":
                    name.Alias = "Float";
                    name.Name = "java.lang.Float";
                    name.JdbcType = "Float";
                    break;
                case "double":
                    name.Alias = "Double";
                    name.Name = "java.lang.Double";
                    name.JdbcType = "Double";
                    break;
                case "decimal":
                    name.Alias = "BigDecimal";
                    name.Name = "java.math.BigDecimal";
                    name.JdbcType = "DECIMAL";
                    break;
                case "char":
                    name.Alias = "java.lang.String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "CHAR";
                    break;
                case "varchar":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "VARCHAR";
                    break;
                case "text":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "VARCHAR";
                    break;
                case "longtext":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "LONGVARCHAR";
                    break;
                case "date":
                     name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "DATE";
                    break;
                case "datetime":
                     name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "TIMESTAMP";
                    break;
                case "time":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "VARCHAR";
                    break;
            }
            return name;
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 专供获取数据库用
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private IDataReader ExecuteReader(string strSQL, string connectionString)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
        }
        #endregion
    }
}
